package org.yystory.ui;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class BuildServlet extends HttpServlet {

	private static final long serialVersionUID = 9071992738961805153L;

	public BuildServlet() {
		super();
	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		ApplicationContext context = WebApplicationContextUtils
				.getWebApplicationContext(this.getServletContext());
		JdbcTemplate jdbcTemplate = (JdbcTemplate) context
				.getBean("jdbcTemplate");
		jdbcTemplate.execute("DROP TABLE IF EXISTS `favourite`");
		jdbcTemplate.execute("DROP TABLE IF EXISTS `sequence`");
		jdbcTemplate.execute("DROP TABLE IF EXISTS `story`");
		jdbcTemplate.execute("DROP TABLE IF EXISTS `user`");
		jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `user` ("
				+ " `id` INT NOT NULL AUTO_INCREMENT ,"
				+ " `userid` INT NOT NULL ," + " PRIMARY KEY (`id`) ) "
				+ " ENGINE = InnoDB");
		jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `story` ("
				+ " `id` INT NOT NULL AUTO_INCREMENT ,"
				+ "`userid` INT NOT NULL ,"
				+ " `name` VARCHAR(100) NOT NULL UNIQUE,"
				+ "`date` DATETIME NOT NULL ," + " PRIMARY KEY (`id`) )"
				+ " ENGINE = InnoDB");
		jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `sequence` ("
				+ " `id` INT NOT NULL AUTO_INCREMENT ,"
				+ " `storyid` INT NOT NULL ," + " `userid` INT NOT NULL ,"
				+ " `length` INT NOT NULL ," + " `ordernum` INT NOT NULL ,"
				+ " `flower` INT NOT NULL ," + " `egg` INT NOT NULL ,"
				+ " `data` LONGBLOB NOT NULL ," + " `date` DATETIME NOT NULL ,"
				+ " PRIMARY KEY (`id`) ,"
				+ " INDEX `fk_sequence_story_idx` (`storyid` ASC) ,"
				+ " CONSTRAINT `fk_sequence_story`"
				+ " FOREIGN KEY (`storyid` )" + " REFERENCES `story` (`id` )"
				+ " ON DELETE RESTRICT" + " ON UPDATE RESTRICT)"
				+ " ENGINE = InnoDB");
		jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `favourite` ("
				+ " `id` INT NOT NULL AUTO_INCREMENT ,"
				+ " `userid` INT NOT NULL ," + " `storyid` INT NOT NULL ,"
				+ " `sequenceid` INT NOT NULL ," + " PRIMARY KEY (`id`) ,"
				+ " INDEX `fk_favourite_story_idx` (`storyid` ASC) ,"
				+ " INDEX `fk_favourite_sequence_idx` (`sequenceid` ASC) ,"
				+ " CONSTRAINT `fk_favourite_story`"
				+ " FOREIGN KEY (`storyid` )" + " REFERENCES `story` (`id` )"
				+ " ON DELETE RESTRICT" + " ON UPDATE RESTRICT,"
				+ " CONSTRAINT `fk_favourite_sequence`"
				+ " FOREIGN KEY (`sequenceid` )"
				+ " REFERENCES `sequence` (`id` )" + " ON DELETE RESTRICT"
				+ " ON UPDATE RESTRICT)" + " ENGINE = InnoDB");
	}
}
